#include "database.h"
#include <QDebug>
#include <QMessageBox>

database::database(QObject *parent)
    : QObject{parent}
{}

bool database::initMysql()
{
    QSqlDatabase mysqlConn = QSqlDatabase::addDatabase("QMYSQL", "mysql_conn");
    //    dbConn.setHostName("10.35.19.193");
    //    dbConn.setPort(3336);
    //    dbConn.setDatabaseName("my2");
    //    dbConn.setUserName("root");
    //    dbConn.setPassword("good@2022");

//    mysqlConn.setHostName("10.35.19.121");
//    mysqlConn.setPort(3306);
//    mysqlConn.setDatabaseName("zentao");
//    mysqlConn.setUserName("read");
//    mysqlConn.setPassword("a0gu5TH9lE7MUD1p");
//    qDebug() << mySqlInfo.hostName<<mySqlInfo.port<<mySqlInfo.dbName<<mySqlInfo.userName<<mySqlInfo.password<<mySqlInfo.dbdesc;
    mysqlConn.setHostName(mySqlInfo.hostName);
    mysqlConn.setPort(mySqlInfo.port.toInt());
    mysqlConn.setDatabaseName(mySqlInfo.dbName);
    mysqlConn.setUserName(mySqlInfo.userName);
    mysqlConn.setPassword(mySqlInfo.password);


    if (mysqlConn.open()) {
        qDebug() << "连接数据库(mysql)成功!";
        return true;
    } else {
        QSqlError err1 = mysqlConn.lastError();
        QString errStr = "连接数据库失败! " + err1.text();
        qDebug() << errStr;
        return false;
    }
}

bool database::initSqlite()
{
    QSqlDatabase sqliteConn = QSqlDatabase::addDatabase("QSQLITE", "sqlite_conn");
    sqliteConn.setDatabaseName("./cfg.db");
    if (sqliteConn.open()) {
        qDebug() << "连接数据库(sqlite)成功!";

        return true;
    } else {
        QString errStr = "连接数据库失败! " + sqliteConn.lastError().text();
        qDebug() << errStr;
        return false;
    }
}

bool database::initCfgData()
{
    QSqlQueryModel *model = new QSqlQueryModel;
    model->setQuery("select hostip,hostport, dbname,username,pwd,dbdesc from dbinfo where dbname ='zentao' and dbtype='mysql' ",
                    QSqlDatabase::database("sqlite_conn"));

    // int column= model->columnCount(); //获得列数
    int row = model->rowCount(); // 获得行数

    for (int i = 0; i < row; i++) {

        mySqlInfo.hostName=model->index(i, 0).data().toString();
        mySqlInfo.port=model->index(i, 1).data().toString();
        mySqlInfo.dbName=model->index(i, 2).data().toString();
        mySqlInfo.userName=model->index(i, 3).data().toString();
        mySqlInfo.password=model->index(i, 4).data().toString();
        mySqlInfo.dbdesc=model->index(i, 5).data().toString();
    }
    //qDebug() << 1<<mySqlInfo.hostName<<mySqlInfo.port<<mySqlInfo.dbName<<mySqlInfo.userName<<mySqlInfo.password<<mySqlInfo.dbdesc;
    return true;
}

int database::getRecCount(QString strSql)
{
    QSqlQueryModel *dataModel = new QSqlQueryModel;
    dataModel->setQuery(strSql, QSqlDatabase::database("mysql_conn"));
    return dataModel->index(0, 0).data().toInt();
}

QMap<QString, QString> database::getDataDictFrSqlite()
{
    QSqlQueryModel *model = new QSqlQueryModel;
    model->setQuery("select dd_key,dd_value from datadict where dd_type =1 ",
                    QSqlDatabase::database("sqlite_conn"));

    // int column= model->columnCount(); //获得列数
    int row = model->rowCount(); // 获得行数

    QString keyStr, valStr;
    QMap<QString, QString> taskStatus;
    for (int i = 0; i < row; i++) {
        keyStr = model->index(i, 0).data().toString();
        valStr = model->index(i, 1).data().toString();
        qDebug() << "key is:" << keyStr << "values is:" << valStr;
        taskStatus[keyStr] = valStr;
    }
    qDebug() << "mapsize is:" << taskStatus.size();
    return taskStatus;
}

QStringList database::getComboBoxDataBySql(const QString &sqlStr)
{
    qDebug() << "getComboBoxDataBySql:sql is:" << sqlStr;
    QList<QString> dataList;
    QStringList strList;
    strList.clear();
    dataList.clear();
    if (sqlStr.isNull())
        return strList;

    QSqlQueryModel *dataModel = new QSqlQueryModel;
    dataModel->setQuery(sqlStr, QSqlDatabase::database("mysql_conn"));
    int row = dataModel->rowCount(); // 获得行数

    QString valStr;
    for (int i = 0; i < row; i++) {
        valStr = dataModel->index(i, 0).data().toString();
        // qDebug()<< "values is:"<< valStr;
        dataList.append(valStr);
        strList << valStr;
    }
    return strList;
}

QList<PersonHours> database::getPersonHours(QString &userName, QDate &startDate, QDate &endDate)
{
    QList<PersonHours> hoursList;
    hoursList.clear();
    QString strSql
        = QString("select zt.project,zp.name,zt.id,zt.name,sum(ztt.consumed) from zt_taskestimate "
                  "ztt ,zt_task zt ,zt_project zp where  ztt.date >= '%1' and ztt.date <='%2' and "
                  "ztt.task= zt.id and ztt.account= (select c.account from zt_user c where "
                  "c.realname ='%3' and c.fails  =0 and c.deleted = 1) and zp.id = zt.project and "
                  "zt.deleted =1  GROUP BY ztt.task order by zt.project")
              .arg(startDate.toString("yyyy-MM-dd"))
              .arg(endDate.toString("yyyy-MM-dd"))
              .arg(userName);
    qDebug() << "getPersonHours sql:" << strSql;
    QSqlQueryModel *dataModel = new QSqlQueryModel;
    dataModel->setQuery(strSql, QSqlDatabase::database("mysql_conn"));
    int rownum = dataModel->rowCount(); // 获得行数
    //    int colnum = dataModel->columnCount();
    qDebug() << "rows is:" << rownum;

    for (int i = 0; i < rownum; i++) {
        PersonHours row;

        row.ProjectId = dataModel->index(i, 0).data().toString();
        row.projectName = dataModel->index(i, 1).data().toString();
        row.taskId = dataModel->index(i, 2).data().toString();
        row.taskName = dataModel->index(i, 3).data().toString();
        row.consumed = dataModel->index(i, 4).data().toString();
        //  qDebug()<<"rows consumed is:" <<row.consumed;
        hoursList.append(row);
    }
    return hoursList;
}

QList<GanttTask> database::getTaskDataByPrjName_new(QPageBar *pageBar)
{
    QList<GanttTask> taskList;
    taskList.clear();
    QString strSql = "select " + pageBar->m_strCols + pageBar->m_strWhere + pageBar->m_strOrd
                     + QString("limit %1,%2")
                           .arg(pageBar->m_pageRecCount * (pageBar->m_currentPage - 1))
                           .arg(pageBar->m_pageRecCount);
    qDebug() << "getTaskDataByPrjName2 sql:" << strSql;
    QSqlQueryModel *dataModel = new QSqlQueryModel;
    dataModel->setQuery(strSql, QSqlDatabase::database("mysql_conn"));
    int rownum = dataModel->rowCount(); // 获得行数
    //    int colnum = dataModel->columnCount();
    qDebug() << "rows is:" << rownum;

    for (int i = 0; i < rownum; i++) {
        GanttTask task;

        task.taskTitle = dataModel->index(i, 3).data().toString();
        task.fourthCol = dataModel->index(i, 5).data().toString();
        task.startDate = QDate::fromString(dataModel->index(i, 7).data().toString(), "yyyy-MM-dd");
        task.endDate = QDate::fromString(dataModel->index(i, 8).data().toString(), "yyyy-MM-dd");
        task.fifthCol = dataModel->index(i, 9).data().toString();
        taskList.append(task);
    }
    return taskList;
}

QList<GanttTask> database::getTaskDataByDevName_new(QPageBar *pageBar)
{
    QList<GanttTask> taskList;
    taskList.clear();
    QString strSql = "select " + pageBar->m_strCols + pageBar->m_strWhere + pageBar->m_strOrd
                     + QString("limit %1,%2")
                           .arg(pageBar->m_pageRecCount * (pageBar->m_currentPage - 1))
                           .arg(pageBar->m_pageRecCount);
    qDebug() << "getTaskDataByPrjName2 sql:" << strSql;
    QSqlQueryModel *dataModel = new QSqlQueryModel;
    dataModel->setQuery(strSql, QSqlDatabase::database("mysql_conn"));
    int rownum = dataModel->rowCount(); // 获得行数
    //    int colnum = dataModel->columnCount();
    qDebug() << "rows is:" << rownum;

    for (int i = 0; i < rownum; i++) {
        GanttTask task;

        task.fourthCol = dataModel->index(i, 0).data().toString(); //获取项目名称
        task.taskTitle = dataModel->index(i, 3).data().toString(); //获取任务名称
        task.startDate = QDate::fromString(dataModel->index(i, 7).data().toString(),
                                           "yyyy-MM-dd"); //获取开始日期
        task.endDate = QDate::fromString(dataModel->index(i, 8).data().toString(),
                                         "yyyy-MM-dd");           //获取结束日期
        task.fifthCol = dataModel->index(i, 9).data().toString(); //获取工时

        taskList.append(task);
    }
    return taskList;
}

QList<DeptDevTotalHours> database::getDeptDevTotalHours(QString &startDate, QString &endDate)
{
    QList<DeptDevTotalHours> hoursList;
    hoursList.clear();

    QString strSql = QString("select zu.skype,zu.realname,sum(zt.consumed) from zt_user zu  "
                             "left join zt_taskestimate zt on zu.account = zt.account and zt.`date` <='%1' and zt.`date` >='%2' "
                             "where zu.dept  in (72,77,83,82,104,79,78,81,85,86,87,90,91,92,94,93) and zu.role not in  ('cpo','pmo','om') and zu.deleted =1 group by zu.account,zu.skype order by zu.skype")
                         .arg(endDate)
                         .arg(startDate);


    qDebug() << "getDeptDevTatalHours sql:" << strSql;
    QSqlQueryModel *dataModel = new QSqlQueryModel;
    dataModel->setQuery(strSql, QSqlDatabase::database("mysql_conn"));
    int rownum = dataModel->rowCount(); // 获得行数
    //    int colnum = dataModel->columnCount();
    qDebug() << "rows is:" << rownum;

    for (int i = 0; i < rownum; i++) {
        DeptDevTotalHours task;

        task.teamName = dataModel->index(i, 0).data().toString();
        task.userName = dataModel->index(i, 1).data().toString();
        task.consumed = dataModel->index(i, 2).data().toString();
        hoursList.append(task);
    }
    return hoursList;
}

QList<DevDetailHours> database::getDevDetailHours(QString &userName,
                                                  QString &startDate,
                                                  QString &endDate)
{
    QList<DevDetailHours> hoursList;
    hoursList.clear();

    QString strSql
        = QString("select zt.task,zp.name,zt2.name,zt2.status,zt.`date`,zt.consumed,zt.work from "
                  "zt_taskestimate zt,zt_user zu,zt_task zt2,zt_project zp                         "
                  "                                                                                "
                  "                                      where zt.`date` <='%1' and zt.`date` "
                  ">='%2' and zu.realname ='%3' and zu.account = zt.account and zt2.id = zt.task "
                  "and zp.id =zt2.project order by zu.realname")
              .arg(endDate)
              .arg(startDate)
              .arg(userName);

    qDebug() << "getDevDetailHours sql:" << strSql;
    QSqlQueryModel *dataModel = new QSqlQueryModel;
    dataModel->setQuery(strSql, QSqlDatabase::database("mysql_conn"));
    int rownum = dataModel->rowCount(); // 获得行数
    //    int colnum = dataModel->columnCount();
    qDebug() << "rows is:" << rownum;

    for (int i = 0; i < rownum; i++) {
        DevDetailHours task;

        task.projectName = dataModel->index(i, 1).data().toString();
        ;
        task.taskName = dataModel->index(i, 2).data().toString();
        task.taskStatus = dataModel->index(i, 3).data().toString();
        task.writeDate = dataModel->index(i, 4).data().toString();
        task.consumed = dataModel->index(i, 5).data().toString();
        task.taskDesc = dataModel->index(i, 6).data().toString();

        hoursList.append(task);
    }
    return hoursList;
}

bool database::getTaskesTimateFrTaskId(QString &tasksId, QDate &startDate, QDate &endDate)
{
    QSqlQueryModel *taskesTimateModel = new QSqlQueryModel;
    QString startStr = startDate.toString("yyyy-MM-dd");
    QString endStr = endDate.toString("yyyy-MM-dd");
    QString sqlStr
        = QString(
              "select zt.task ,zt.`date` ,zt.consumed ,zu.realname from zt_taskestimate zt ,zt_user zu \
                                 where zt.task in(%1) and zt.`date` <='%2' and zt.`date` >='%3' and zu.account = zt.account")
              .arg(tasksId)
              .arg(endStr)
              .arg(startStr);
    taskesTimateModel->setQuery(sqlStr, QSqlDatabase::database("mysql_conn"));
    int column = taskesTimateModel->columnCount(); //获得列数
    int row = taskesTimateModel->rowCount();       // 获得行数
    qDebug() << "sql is:" << sqlStr;
    qDebug() << "column is:" << column << "row: " << row;
    return true;
}

void database::getPagingInfo(QPageBar *pageBar)
{
    qDebug() << "getPagingInfo sql:" << pageBar->m_strWhere;
    QSqlQueryModel *dataModel = new QSqlQueryModel;
    dataModel->setQuery("select count(*) " + pageBar->m_strWhere,
                        QSqlDatabase::database("mysql_conn"));
    pageBar->m_recTotalCount = dataModel->index(0, 0).data().toInt();

    //   pageBar->m_pageRecCount = pageSize;
    pageBar->m_pageTotalCount = (pageBar->m_recTotalCount - 1) / pageBar->m_pageRecCount + 1;
    if (pageBar->m_pageTotalCount >= 3)
        pageBar->setCount(pageBar->m_pageTotalCount, 3);
    else
        pageBar->setCount(pageBar->m_pageTotalCount, pageBar->m_pageTotalCount);
    pageBar->m_currentPage = 1;

    qDebug() << "rectotal:" << pageBar->m_recTotalCount << "pagetotal:" << pageBar->m_pageTotalCount
             << "pagesize:" << pageBar->m_pageRecCount << "curpage:" << pageBar->m_currentPage;
}

QList<PrjVerPlan> database::getProjectVerPlan(const QString &productName)
{
    QString strSql = QString("select  "
                             "zpp.Id,zpp.title,zpp.`begin`,zpp.`"
                             "end`,zpp.`desc`  from zt_product "
                             "zp,zt_productplan zpp where "
                             "zpp.product = zp.id and zp.deleted "
                             "=1 and zp.status = 'normal' and "
                             "zpp.deleted =1 and zp.name ='%1' and "
                             "YEAR(zpp.begin)>='2023' ORDER BY "
                             "zpp.title")
                         .arg(productName);
    QList<PrjVerPlan> recList;
    recList.clear();
    QSqlQueryModel *dataModel = new QSqlQueryModel;
    dataModel->setQuery(strSql, QSqlDatabase::database("mysql_conn"));
    int rownum = dataModel->rowCount(); // 获得行数
    //    int colnum = dataModel->columnCount();
    qDebug() << "rows is:" << rownum;
    qDebug() << "getProjectVerPlan:" << strSql;

    for (int i = 0; i < rownum; i++) {
        PrjVerPlan rec;

        rec.verPlanId = dataModel->index(i, 0).data().toString();
        rec.verPlanName = dataModel->index(i, 1).data().toString();
        rec.verStartDate = dataModel->index(i, 2).data().toString();
        rec.verEndDate = dataModel->index(i, 3).data().toString();
        rec.verPlanDesc = dataModel->index(i, 4).data().toString();

        recList.append(rec);
    }
    return recList;
}

QList<StoryTaskByVer> database::getStoryTaskByVerPlan(const QString &verPlanId)
{
    QString strSql = QString("select zs.title ,zt.name,zt.status ,zt.estStarted ,zt.deadline ,zu.realname,zt.consumed  from zt_story zs left join zt_task zt on zs.id = zt.story  left join zt_user zu on zt.assignedTo = zu.account where  zs.plan ='%1' order by zs.id").arg(verPlanId);
    qDebug() << "getStoryTaskByVerPlan is:" << strSql;
  //  QMap<QString, QList<StoryTaskByVer>> recMap;
    QList<StoryTaskByVer> recList;
    recList.clear();

    QSqlQueryModel *dataModel = new QSqlQueryModel;
    dataModel->setQuery(strSql, QSqlDatabase::database("mysql_conn"));
    int rownum = dataModel->rowCount(); // 获得行数
    //    int colnum = dataModel->columnCount();
    qDebug() << "getStoryTaskByVerPlan rows is:" << rownum;

    for (int i = 0; i < rownum; i++) {
        StoryTaskByVer rec;

        rec.storyName = dataModel->index(i, 0).data().toString();
        rec.taskName = dataModel->index(i, 1).data().toString();
        rec.taskStatus = dataModel->index(i, 2).data().toString();
        rec.taskStartDate = QDate::fromString(dataModel->index(i, 3).data().toString(),"yyyy-M-d");
        rec.taskEndDate = QDate::fromString(dataModel->index(i, 4).data().toString(),"yyyy-M-d");
        rec.taskUser=dataModel->index(i, 5).data().toString();
        rec.taskConsumed = dataModel->index(i, 6).data().toString();
        recList.append(rec);

       // recMap.insert(rec.storyName,recList);
    }
    return recList;
}
